<?php


class Blackbird_Model_ContentBlocks extends Zend_Db_Table
{
	function fetchBanners($params)
	{
		$categoryId = (int)$params['categoryId'];
		$limit = (int)$params['limit'];
		$db = $this->getAdapter();
		$sql = 'select bannerId, name, slug, image, url, customCode'
			. "\n	from bb_banner"
			. "\n	where isPublished <> 0 and categoryId = $categoryId"
			. "\n		and publishedAt < CURRENT_TIMESTAMP and (expiresAt is null or expiresAt > CURRENT_TIMESTAMP)"
			. "\n		and (totalImpressions = 0 or impressions < totalImpressions - 1)"
			. "\n	order by ordering"
			. "\n	limit $limit"
			;
		$banners = $db->fetchAll($sql);
		if (count($banners) > 0)
		{
			$bannerIds = array();
			foreach ($banners as $banner)
				$bannerIds[] = $banner->bannerId;
			$sql = 'update bb_banner'
				. "\n	set impressions = impressions + 1"
				. "\n	where bannerId in (" . implode(', ', $bannerIds) . ')'
				;
			$db->query($sql);
		}
		return $banners;
	}

	function fetchLatestArticles($params)
	{
		$categoryId = (int)$params['categoryId'];
		$limit = (int)$params['limit'];
		$db = $this->getAdapter();
		$sql = 'select a.articleId, a.title, a.slug, a.summary, a.picture, c.name categoryName, c.slug categorySlug, '
			. "\n		mc.name mainCategoryName, mc.slug mainCategorySlug"
			. "\n	from bb_article a"
			. "\n		inner join (select articleId, min(categoryId) categoryId"
			. "\n				from bb_articlecategory"
			. "\n				group by articleId) ac on ac.articleId = a.articleId"
			. "\n		inner join bb_category c on c.categoryId = ac.categoryId"
			. "\n		inner join bb_category mc on case when c.parentId is null then mc.categoryId = c.categoryId"
			. "\n			else mc.parentId is null and mc.lft < c.lft and mc.rgt > c.rgt end"
			. "\n	where a.isPublished <> 0" . ($categoryId > 0 ? ' and c.categoryId = ' . $categoryId : '')
			. "\n		and a.publishedAt < CURRENT_TIMESTAMP and (a.expiresAt is null or a.expiresAt > CURRENT_TIMESTAMP)"
			. "\n	order by a.publishedAt desc"
			. "\n	limit $limit";
		$articles = $db->fetchAll($sql);
		foreach ($articles as $article)
		{
			$article->categoryUrl = BASE_URL . '/articles'
				. ($article->mainCategorySlug != $article->categorySlug ? '/' . $article->mainCategorySlug : '')
				. '/' . $article->categorySlug;
			$article->url = $article->categoryUrl . '/' . $article->slug;
		}
		return $articles;
	}

	function fetchLatestPoll($params)
	{
		$db = $this->getAdapter();
		$sql = 'select pollId, question, slug'
			. "\n	from bb_poll"
			. "\n	where isPublished <> 0"
			. "\n		and publishedAt < CURRENT_TIMESTAMP and (expiresAt is null or expiresAt > CURRENT_TIMESTAMP)"
			. "\n	order by publishedAt desc"
			. "\n	limit 1";
		$poll = $db->fetchRow($sql);
		if (false != $poll)
		{
			$sql = 'select pollOptionId, answer'
				. "\n	from bb_polloption"
				. "\n	where pollId = " . (int)$poll->pollId
				;
			$poll->options = $db->fetchAll($sql);
		}
		return $poll;
	}

	function fetchMenu($params)
	{
		$menuId = (int)$params['menuId'];
		$db = $this->getAdapter();
		$sql = 'select menuId, name'
			. "\n	from bb_menuitem"
			. "\n	where menuId = $menuId and isPublished"
			;
		$menu = $db->fetchRow($sql);
		$sql = 'select menuItemId, parentId, lft, rgt, name, slug, picture, isVisible'
			. "\n	from bb_menuitem"
			. "\n	where menuId = $menuId and isPublished"
			. "\n	order by lft, rgt"
			;
		$menu->items = array();
		$current =& $menu->items;
		// Although, $itemStack is treated as a stack I can't use array_push() or array_pop() because we need to
		// keep references there.
		$itemStack = array();
		$parentStack = array();
		$lastParent = null;
		foreach ($db->fetchAll($sql) as $item)
		{
			$item->items = array();
			if ($lastParent != $item->parentId)
			{
				// Needed a temporary $parent variable to avoid a circular reference.
				// Read the next three lines as:
				// $current =& array_peek($itemStack);
				$parent =& $itemStack[count($itemStack) - 1];
				array_pop($itemStack);
				$current =& $parent;
				$lastParent = array_pop($parentStack);
			}
			$current[] = $item;
			if ($item->rgt > ($item->lft + 1))
			{
				//echo "entering {$item->name}, new parentId: $item->parentId<br/>\n";
				$itemStack[] =& $current;
				array_push($parentStack, $lastParent);
				$current =& $item->items;
				$lastParent = $item->menuItemId;
			}
		}
		return $menu;
	}

	function fetchArticlesCategories()
	{
		$sql = 'select c.categoryId, c.name, c.parentId'
			. "\n	from bb_category c"
			. "\n	where c.component = 'Articles'"
			. "\n	order by c.lft, c.rgt"
			;
		$db = $this->getAdapter();
		$categories = array();
		foreach ($db->fetchAll($sql) as $row)
			$categories[$row->categoryId] = ($row->parentId > 0 ? '  ' : '') . $row->name;
		return $categories;
	}

	function fetchBannersCategories()
	{
		$sql = 'select c.categoryId, c.name, c.parentId'
			. "\n	from bb_category c"
			. "\n	where c.component = 'Banners'"
			. "\n	order by c.lft, c.rgt"
			;
		$db = $this->getAdapter();
		$categories = array();
		foreach ($db->fetchAll($sql) as $row)
			$categories[$row->categoryId] = ($row->parentId > 0 ? '  ' : '') . $row->name;
		return $categories;
	}

	function fetchMenus()
	{
		$sql = 'select m.menuId, m.name'
			. "\n	from bb_menu m"
			. "\n	order by m.name"
			;
		$db = $this->getAdapter();
		$menus = array();
		foreach ($db->fetchAll($sql) as $row)
			$menus[$row->menuId] = $row->name;
		return $menus;
	}
}

